How to Display the Total from Different Tables in One Query

TGoldenEye

How to Display a Total from Different Table in One Query

In this How To, I will show you how to get the total value of some fields in different tables and display in one place. I will use my student database as the example in this How To. Briefly, I have a student, enrollment, class, and payment table. I want to get the total of class fee from the Class table and the total of payment from the Payment table for each student in one query/ place.

Relationship of Table

Per the relationship of the tables below, you will see the class fee and the payment amount in different table. However they are linked to the Student table. When the students enrolled the class, the students now owe the fee to school until they make a payment.

Example of Student table

Example of Class table

Example of Enrollment table

The fee is set to each class. When the student registers or enrolls each class, the student must pay for fee of each class. The student can make a payment while enrolls the class or makes a payment later. Each record of the class enrollment is saved in the Enrollment table. However, the fee is not saved in this table. The fee is just linked by the class enrolled for each student.

It will keep you fit and you will never be able to use it successfully with others, unless you have viagra store in canada successfully experienced it. Majorly, male impotence or ED occurs due to narrowness of vessels that do not carry sufficient blood to male genital part to cause an erection. viagra online in uk If the doctor finds it appropriate, he may prescribe generic sample viagra sildenafil for your impotence treatment. Buying generic medications online is the most affordable treatment visit now now female levitra and the most effective penis enlargement technique available in the market as a copy of original branded drug.

Example of Payment table

In the example of this How To, the payment is not specific to the class name. Each payment is saved in this table for each student with the payment date. It does not save the total of payment for each student.

Get Total of Class Fee

Per the enrollment table, there is no Total field for the class fee. So we have to create a Select Query to link the Student table, the Enrollment table, and the Class table and to get the Sum of the class fee for each group of the student. In this example, we select only 3 fields to display in the query.

Datasheet View of Total Class Fee

The result of the Select Query above displays below. It will display the total fee of all classes for each student. There will be only one record for each student as it is set to Group by StudentID.

Get Total of Payment

We can get the Total Payment for each student by using the Select Query to link the Student table and the Payment table together. It will display the total payment for each student in the result below.

Datasheet View of Payment Amount

Combine Total Fee and Payment in One Query

Per the steps above, we have two separate queries to get the total of Class Fee and the total of Payment. Now we need to create the 3rd query to combine those two Select Queries. We can create a simple query following the steps below:

  1. Create a simple query
  2. Insert two Select Queries (EnrollSum, PaymentSum)
  3. Link two queries by drag the StudentID field from one table to StudentID fields of another table
  4. You can set a new name of the SumOfClassFee field (Ex: Enroll)
  5. You can set a new name of the SumOfPayment Amout field (Ex: Payment)
  6. You can create another field (Balance field) from [Sumofclassfee] –[sumofpayment amount]
  7. You can set Criteria >0 if you want to display only the Balance >0 (students still owe some fee)
  8. Save new query as PaymentOwed

Datasheet View of Total Fee and Payment in One Query

 Display Total Fee and Payment on Form

You can call the Enroll Fee, the Payment, and the Balance from PaymentOwed query. In the example below, I will call those fields on the Student form. It will display the total of enrolled fee, total of payment, and the balance of the selected student. It is linked by the StudentID.

You will need to add three textboxes into the student form.

The 1st textbox names txtEnrolledAmount
The 2nd textbox names txtTotalPaid
The 3rd textbox names txtBalance

You can create a function to get all of the total and called the function under the Form_Load() Event Procedure. The example of VB code is shown below.

Private Sub Form_Load()
   Call GetTotal ()
End Sub
Function GetTotal()
Dim Enrolled, TotalPaid, TotalBalance As Double
Enrolled = DLookup("enroll", "PaymentOwed", "[studentid] = " & Me.StudentID & "")
TotalPaid = DLookup("payment", "PaymentOwed", "[studentID] = " & Me.StudentID & "")
TotalBalance = Enrolled - TotalPaid
Me.txtEnrolledAmount.Value = Enrolled
Me.txtTotalPaid.Value = TotalPaid
End Function

The balance is not set in the VB code above, however we can set the Control Source from the two textboxes as =[txtEnrolledAmount]-[txtTotalPaid] as shown below. We can also call it directly from the query above.

Related posts